{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "AnDLLTlAVp98"
   },
   "source": [
    "# Practical 3B: Pandas \n",
    "\n",
    "Upon completion of this session you should be able to:\n",
    "\n",
    "   - use Python Pandas to manipulate the data and files\n",
    "\n",
    "---\n",
    "- Materials in this module include resources collected from various open-source online repositories.\n",
    "- Jupyter source file can be downloaded from clouddeakin SIT384 > weekly resources or https://github.com/gaoshangdeakin/SIT384-Jupyter\n",
    "- If you found any issue/bug for this document, please submit an issue at [https://github.com/gaoshangdeakin/SIT384/issues](https://github.com/gaoshangdeakin/SIT384/issues)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6y00ux36Vp-G"
   },
   "source": [
    "# Pandas\n",
    "\n",
    "Credits: The following are notes taken while working through [Python for Data Analysis](http://www.amazon.com/Python-Data-Analysis-Wrangling-IPython/dp/1449319793) by Wes McKinney\n",
    "\n",
    "Pandas is an external library. To use it, you need to import it first. There are several ways:\n",
    "* import pandas\n",
    "* from pandas import tools\n",
    "* import pandas as pd\n",
    "\n",
    "## Table of Content\n",
    "\n",
    "1. Series\n",
    "2. DataFrame\n",
    "3. Reindexing\n",
    "1. Dropping Entries\n",
    "1. Indexing, Selecting, Filtering\n",
    "1. Arithmetic and Data Alignment\n",
    "1. Function Application and Mapping\n",
    "1. Sorting and Ranking\n",
    "1. Axis Indices with Duplicate Values\n",
    "1. Summarizing and Computing Descriptive Statistics\n",
    "1. Cleaning Data \n",
    "1. Input and Output  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "-XdOTaSAVp-K"
   },
   "outputs": [],
   "source": [
    "#different ways to importing pandas\n",
    "from pandas import Series, DataFrame\n",
    "import pandas as pd\n",
    "\n",
    "import numpy as np\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "TBOp1orOVp-X"
   },
   "source": [
    "## 1. Series\n",
    "\n",
    "A Series is a one-dimensional array-like object containing an array of data and an associated array of data labels.  The data can be any NumPy data type and the labels are the Series' index."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "_pwF5TWHVp-a"
   },
   "source": [
    "Create a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3TcFNhwuVp-c"
   },
   "outputs": [],
   "source": [
    "ser_1 = Series([1, 1, 2, -3, -5, 8, 13])\n",
    "ser_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "lOSv3qBHVp-m"
   },
   "source": [
    "Get the array representation of a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "l5DE_8trVp-p"
   },
   "outputs": [],
   "source": [
    "ser_1.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "JCxtRtH0Vp-y"
   },
   "source": [
    "Index objects are immutable and hold the axis labels and metadata such as names and axis names.\n",
    "\n",
    "Get the index of the Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "36YpUAW3Vp-1"
   },
   "outputs": [],
   "source": [
    "ser_1.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "B3osaFY7Vp--"
   },
   "source": [
    "Create a Series with a custom index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "sJwZEpqgVp_C"
   },
   "outputs": [],
   "source": [
    "ser_2 = Series([1, 1, 2, -3, -5], index=['a', 'b', 'c', 'd', 'e'])\n",
    "ser_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "DVmNWyMqVp_L"
   },
   "source": [
    "Get a value from a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "dIG2FbueVp_P"
   },
   "outputs": [],
   "source": [
    "ser_2[4] == ser_2['e']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "KQpUj2qPVp_a"
   },
   "source": [
    "Get a set of values from a Series by passing in a list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "PxWldE0pVp_e"
   },
   "outputs": [],
   "source": [
    "ser_2[['c', 'a', 'b']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ktiZh9Z9Vp_r"
   },
   "source": [
    "Get values great than 0:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "X50SBkYeVp_x"
   },
   "outputs": [],
   "source": [
    "ser_2[ser_2 > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "2rtn6AO6Vp_7"
   },
   "source": [
    "Scalar multiply:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "l4LtS-YDVp_-"
   },
   "outputs": [],
   "source": [
    "ser_2 * 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1CiehcB4VqAH"
   },
   "source": [
    "Apply a numpy math function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ZveZARxpVqAJ"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "#The np.exp() is used to calculate the exponential of all elements in the input array\n",
    "np.exp(ser_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "5HKLlcqNVqAR"
   },
   "source": [
    "A Series is like a fixed-length, ordered dict.  \n",
    "\n",
    "Create a series by passing in a dict:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "UOqi_C0tVqAU"
   },
   "outputs": [],
   "source": [
    "dict_1 = {'foo' : 100, 'bar' : 200, 'baz' : 300}\n",
    "ser_3 = Series(dict_1)\n",
    "ser_3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "UyfGTS8eVqAf"
   },
   "source": [
    "Re-order a Series by passing in an index (indices not found are NaN):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "tyZQwUsoVqAi"
   },
   "outputs": [],
   "source": [
    "#There is no value for index item 'qux', it will be set with value 'NaN'#\n",
    "#In addition , Nan is considered as a float, pleasa refer the IEEE 754 (https://en.wikipedia.org/wiki/IEEE_754).\n",
    "#As a result, the dtype will changed into float64\n",
    "index = ['foo', 'bar', 'baz', 'qux']\n",
    "ser_4 = Series(dict_1, index=index)\n",
    "ser_4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "7BFvDkiNVqAu"
   },
   "source": [
    "Check for NaN with the pandas method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "MmRFinnvVqAw"
   },
   "outputs": [],
   "source": [
    "pd.isnull(ser_4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "EvY8SIWJVqA5"
   },
   "source": [
    "Check for NaN with the Series method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "B2LsRxgBVqBB"
   },
   "outputs": [],
   "source": [
    "ser_4.isnull()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "gGsjvUD7VqBO"
   },
   "source": [
    "Series automatically aligns differently indexed data in arithmetic operations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "fCXZ8pwjVqBR"
   },
   "outputs": [],
   "source": [
    "ser_3 + ser_4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "bkKu3bQmVqBb"
   },
   "source": [
    "Name a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "39fDo_00VqBe"
   },
   "outputs": [],
   "source": [
    "ser_4.name = 'foobarbazqux'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "DZfcCr63VqBl"
   },
   "source": [
    "Name a Series index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "IX825eESVqBo"
   },
   "outputs": [],
   "source": [
    "ser_4.index.name = 'label'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "yAyAWBA_VqB2"
   },
   "outputs": [],
   "source": [
    "ser_4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "4OgEA3IcVqB-"
   },
   "source": [
    "Rename a Series' index in place:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "OKu3DLAwVqCB"
   },
   "outputs": [],
   "source": [
    "ser_4.index = ['fo', 'br', 'bz', 'qx']\n",
    "ser_4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "nNPE9JVCVqCI"
   },
   "source": [
    "## 2. DataFrame\n",
    "\n",
    "A DataFrame is a tabular data structure containing an ordered collection of columns.  Each column can have a different type.  DataFrames have both row and column indices and is analogous to a dict of Series.  Row and column operations are treated roughly symmetrically.  Columns returned when indexing a DataFrame are views of the underlying data, not a copy.  To obtain a copy, use the Series' copy method.\n",
    "\n",
    "Create a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "z54ymizLVqCM"
   },
   "outputs": [],
   "source": [
    "data_1 = {'state' : ['VA', 'VA', 'VA', 'MD', 'MD'],\n",
    "          'year' : [2012, 2013, 2014, 2014, 2015],\n",
    "          'pop' : [5.0, 5.1, 5.2, 4.0, 4.1]}\n",
    "df_1 = DataFrame(data_1)\n",
    "df_1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "eCYGJPc6VqCW"
   },
   "source": [
    "Create a DataFrame specifying a sequence of columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "iaLNOd6cVqCZ"
   },
   "outputs": [],
   "source": [
    "df_2 = DataFrame(data_1, columns=['year', 'state', 'pop'])\n",
    "df_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wWk9CCj5VqCk"
   },
   "source": [
    "Like Series, columns that are not present in the data are NaN:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "IWyA3xf2VqCn"
   },
   "outputs": [],
   "source": [
    "df_3 = DataFrame(data_1, columns=['year', 'state', 'pop', 'unempl'])\n",
    "df_3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Fum6fsH9VqCv"
   },
   "source": [
    "Retrieve a column by key, returning a Series:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "vj9PSdgpVqC0"
   },
   "outputs": [],
   "source": [
    "df_3['state']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "8pmCpwBHVqC9"
   },
   "source": [
    "Retrive a column by attribute, returning a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Cm0gtqyWVqDA"
   },
   "outputs": [],
   "source": [
    "df_3.year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "YceQ_iH4VqDM"
   },
   "source": [
    "Retrieve a row by position:\n",
    "Python has provide three function, ix(), loc(), iloc ()  to select rows and columns in Pandas DataFrames\n",
    "\n",
    "*   Selecting data by label or by a conditional statment (.loc)\n",
    "*   Selecting data by row numbers (.iloc)\n",
    "*   Selecting in a hybrid approach (.ix) (**now Deprecated in Pandas 0.20.1**)\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "sHMotUPTs0YY"
   },
   "outputs": [],
   "source": [
    "df_3.loc[:,['year']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "yeDULL4Fszhz"
   },
   "outputs": [],
   "source": [
    "df_3.iloc[:,2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ZtFl2fS6VqDP"
   },
   "outputs": [],
   "source": [
    "df_3.ix[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "uDPvcGNzVqDV"
   },
   "source": [
    "Update a column by assignment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "5IufJ-9xVqDX"
   },
   "outputs": [],
   "source": [
    "df_3['unempl'] = np.arange(5)\n",
    "df_3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aHMg_brbVqDe"
   },
   "source": [
    "Assign a Series to a column (note if assigning a list or array, the length must match the DataFrame, unlike a Series):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "jixfvr5FVqDg"
   },
   "outputs": [],
   "source": [
    "unempl = Series([6.0, 6.0, 6.1], index=[2, 3, 4])\n",
    "df_3['unempl'] = unempl\n",
    "df_3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "qELq5QK6VqDm"
   },
   "source": [
    "Assign a new column that doesn't exist to create a new column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "2S2xuaKXVqDo"
   },
   "outputs": [],
   "source": [
    "df_3['state_dup'] = df_3['state']\n",
    "df_3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "dhJcB2BQVqDv"
   },
   "source": [
    "Delete a column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "NlWtYYSCVqDx"
   },
   "outputs": [],
   "source": [
    "del df_3['state_dup']\n",
    "df_3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Ddd8hj4SVqEB"
   },
   "source": [
    "Create a DataFrame from a nested dict of dicts (the keys in the inner dicts are unioned and sorted to form the index in the result, unless an explicit index is specified):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "TPA5hrZ0VqED"
   },
   "outputs": [],
   "source": [
    "pop = {'VA' : {2013 : 5.1, 2014 : 5.2},\n",
    "       'MD' : {2014 : 4.0, 2015 : 4.1}}\n",
    "df_4 = DataFrame(pop)\n",
    "df_4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "4EzQ3P8KVqEP"
   },
   "source": [
    "Transpose the DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "TqsUuLPmVqET"
   },
   "outputs": [],
   "source": [
    "df_4.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "IQWNC69YVqEd"
   },
   "source": [
    "Create a DataFrame from a dict of Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "4y1wwy2mVqEk"
   },
   "outputs": [],
   "source": [
    "data_2 = {'VA' : df_4['VA'][1:],\n",
    "          'MD' : df_4['MD'][2:]}\n",
    "df_5 = DataFrame(data_2)\n",
    "df_5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "BwINvzz7VqEs"
   },
   "source": [
    "Set the DataFrame index name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "fN6oBt-1VqEu"
   },
   "outputs": [],
   "source": [
    "df_5.index.name = 'year'\n",
    "df_5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "F8kPSYueVqE1"
   },
   "source": [
    "Set the DataFrame columns name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "2yDKE5fzVqE3"
   },
   "outputs": [],
   "source": [
    "df_5.columns.name = 'state'\n",
    "df_5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "T-KQM90iVqE-"
   },
   "source": [
    "Return the data contained in a DataFrame as a 2D ndarray:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "vEweDfAAVqFA"
   },
   "outputs": [],
   "source": [
    "df_5.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "pdl5EX6iVqFG"
   },
   "source": [
    "If the columns are different dtypes, the 2D ndarray's dtype will accomodate all of the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "z16TT5rTVqFI"
   },
   "outputs": [],
   "source": [
    "df_3.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "CgjK5r68VqFP"
   },
   "source": [
    "## 3. Reindexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "LNuzdtueVqFS"
   },
   "source": [
    "Create a new object with the data conformed to a new index.  Any missing values are set to NaN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "I8s6ucQ7VqFV"
   },
   "outputs": [],
   "source": [
    "df_3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1UOgSqPTVqFa"
   },
   "source": [
    "Reindexing rows returns a new frame with the specified index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "VXhmFrZpVqFc"
   },
   "outputs": [],
   "source": [
    "df_3.reindex(list(reversed(range(0, 6))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "8fkBZ30dVqFm"
   },
   "source": [
    "Missing values can be set to something other than NaN:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "dEl52D4zVqFo"
   },
   "outputs": [],
   "source": [
    "#for the DataFrame.reindex method\n",
    "#The parameter of \"fill_vaule\" is used to replace missing values. \n",
    "#Defaults to NaN, but can be any “compatible” value.\n",
    "df_3.reindex(range(0, 6), fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "RB34GsmZVqFu"
   },
   "source": [
    "Interpolate ordered data like a time series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "DEdjbE59VqF3"
   },
   "outputs": [],
   "source": [
    "ser_5 = Series(['foo', 'bar', 'baz'], index=[0, 2, 4])\n",
    "ser_5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "w2DTs-dqVqF9"
   },
   "outputs": [],
   "source": [
    "ser_5.reindex(range(5), method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "O2hyG-5QVqGD"
   },
   "outputs": [],
   "source": [
    "ser_5.reindex(range(5), method='bfill')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Eao5SKmkVqGK"
   },
   "source": [
    "Reindex columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "SYyDMuwCVqGL"
   },
   "outputs": [],
   "source": [
    "df_3.reindex(columns=['state', 'pop', 'unempl', 'year'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "BQqYQih2VqGR"
   },
   "source": [
    "Reindex rows and columns while filling rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "rmFF6FNeVqGU"
   },
   "outputs": [],
   "source": [
    "df_3.reindex(index=list(reversed(range(0, 6))),\n",
    "             fill_value=0,\n",
    "             columns=['state', 'pop', 'unempl', 'year'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "BE5eRcXOVqGl"
   },
   "source": [
    "## 4. Dropping Entries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "5Y3mva6eVqGs"
   },
   "source": [
    "Drop rows from a Series or DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Os6ibbB7VqGu"
   },
   "outputs": [],
   "source": [
    "df_7 = df_3.drop([0, 1])\n",
    "df_7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "lawC-BiqVqG0"
   },
   "source": [
    "Drop columns from a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "zkhkJ8EcVqG2"
   },
   "outputs": [],
   "source": [
    "df_7 = df_7.drop('unempl', axis=1)\n",
    "df_7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "MXp47uJUVqG-"
   },
   "source": [
    "## 5. Indexing, Selecting, Filtering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "-PPgwjUdVqHA"
   },
   "source": [
    "Series indexing is similar to NumPy array indexing with the added bonus of being able to use the Series' index values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "pQYJoBzSVqHC"
   },
   "outputs": [],
   "source": [
    "ser_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ypRrxCrwVqHJ"
   },
   "source": [
    "Select a value from a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "knAWkoPaVqHL"
   },
   "outputs": [],
   "source": [
    "ser_2[0] == ser_2['a']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wWIKsQFEVqHS"
   },
   "source": [
    "Select a slice from a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "JHn9zf_9VqHU"
   },
   "outputs": [],
   "source": [
    "ser_2[1:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "sRy_LMkOVqHa"
   },
   "source": [
    "Select specific values from a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "8tONQsPuVqHc"
   },
   "outputs": [],
   "source": [
    "ser_2[['b', 'c', 'd']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "DYz32OuZVqHi"
   },
   "source": [
    "Select from a Series based on a filter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "K2AHMgh1VqHk"
   },
   "outputs": [],
   "source": [
    "ser_2[ser_2 > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "fMwPjiJwVqHt"
   },
   "source": [
    "Select a slice from a Series with labels (note the end point is inclusive):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "7T-AcDpZVqHv"
   },
   "outputs": [],
   "source": [
    "ser_2['a':'b']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "iC9Y1wBFVqHz"
   },
   "source": [
    "Assign to a Series slice (note the end point is inclusive):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "mHf5--veVqH1"
   },
   "outputs": [],
   "source": [
    "ser_2['a':'b'] = 0\n",
    "ser_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "OP-oy46hVqIC"
   },
   "source": [
    "Pandas supports indexing into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "lzIwygmPVqIE"
   },
   "outputs": [],
   "source": [
    "df_3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "QeOEsdopVqIL"
   },
   "source": [
    "Select specified columns from a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HwUfToh4VqIN"
   },
   "outputs": [],
   "source": [
    "df_3[['pop', 'unempl']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1yZLZlNFVqIU"
   },
   "source": [
    "Select a slice from a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "uD1fr91yVqIc"
   },
   "outputs": [],
   "source": [
    "df_3[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "DyMyPPILVqIi"
   },
   "source": [
    "Select from a DataFrame based on a filter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "7snYXnr7VqIj"
   },
   "outputs": [],
   "source": [
    "df_3[df_3['pop'] > 5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "pb2L4LcEVqI9"
   },
   "source": [
    "Select a slice of rows from a DataFrame (note the end point is inclusive):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "O3Na5ja3VqI_"
   },
   "outputs": [],
   "source": [
    "df_3.loc[2:3]\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "gSHp3_GrVqJJ"
   },
   "source": [
    "Select a slice of rows from a specific column of a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "0gbPCZnsVqJO"
   },
   "outputs": [],
   "source": [
    "df_3.loc[0:2, 'pop']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "IWFO4a7NVqJY"
   },
   "source": [
    "Select rows based on an arithmetic operation on a specific row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "EKclLgVdVqJZ"
   },
   "outputs": [],
   "source": [
    "df_3.loc[df_3.unempl > 5.0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ClB-NC_gVqJf"
   },
   "source": [
    "## 6. Arithmetic and Data Alignment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "YCJ5dbCMVqJg"
   },
   "source": [
    "Adding Series objects results in the union of index pairs if the pairs are not the same, resulting in NaN for indices that do not overlap:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "UKWygEpEVqJi"
   },
   "outputs": [],
   "source": [
    "np.random.seed(0)\n",
    "ser_6 = Series(np.random.randn(5),\n",
    "               index=['a', 'b', 'c', 'd', 'e'])\n",
    "ser_6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "UAEiRqmRVqJm"
   },
   "outputs": [],
   "source": [
    "np.random.seed(1)\n",
    "ser_7 = Series(np.random.randn(5),\n",
    "               index=['a', 'c', 'e', 'f', 'g'])\n",
    "ser_7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "-EIx4hpSVqJr"
   },
   "outputs": [],
   "source": [
    "ser_6 + ser_7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "5TGRfaaXVqJ2"
   },
   "source": [
    "Set a fill value instead of NaN for indices that do not overlap:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "jvmB9kVJVqJ4"
   },
   "outputs": [],
   "source": [
    "ser_6.add(ser_7, fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "dJMiIPM8VqJ8"
   },
   "source": [
    "Adding DataFrame objects results in the union of index pairs for rows and columns if the pairs are not the same, resulting in NaN for indices that do not overlap:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "sik9oe_-VqJ-"
   },
   "outputs": [],
   "source": [
    "np.random.seed(0)\n",
    "df_8 = DataFrame(np.random.rand(9).reshape((3, 3)),\n",
    "                 columns=['a', 'b', 'c'])\n",
    "df_8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "O-Ktsa5GVqKD"
   },
   "outputs": [],
   "source": [
    "np.random.seed(1)\n",
    "df_9 = DataFrame(np.random.rand(9).reshape((3, 3)),\n",
    "                 columns=['b', 'c', 'd'])\n",
    "df_9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "O1_4YK6wVqKG"
   },
   "outputs": [],
   "source": [
    "df_8 + df_9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "db9g2CUQVqKK"
   },
   "source": [
    "Set a fill value instead of NaN for indices that do not overlap:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "C2cASM8OVqKM"
   },
   "outputs": [],
   "source": [
    "df_10 = df_8.add(df_9, fill_value=0)\n",
    "df_10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "3j9i77qKVqKQ"
   },
   "source": [
    "Like NumPy, pandas supports arithmetic operations between DataFrames and Series.\n",
    "\n",
    "Match the index of the Series on the DataFrame's columns, broadcasting down the rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ser_8 = df_10.loc[0]\n",
    "df_11 = df_10 - ser_8\n",
    "df_11"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "uES86xMnVqKV"
   },
   "source": [
    "Match the index of the Series on the DataFrame's columns, broadcasting down the rows and union the indices that do not match:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3wIl0FxjVqKY"
   },
   "outputs": [],
   "source": [
    "ser_9 = Series(range(3), index=['a', 'd', 'e'])\n",
    "ser_9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "idZ7bYyNVqKh"
   },
   "outputs": [],
   "source": [
    "df_11 - ser_9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "_HokmoeIVqKp"
   },
   "source": [
    "Broadcast over the columns and match the rows (axis=0) by using an arithmetic method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "rsqMYS4AVqKq"
   },
   "outputs": [],
   "source": [
    "df_10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "AFN2dYfEVqKx"
   },
   "outputs": [],
   "source": [
    "ser_10 = Series([100, 200, 300])\n",
    "ser_10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "h74ZHIamVqK4"
   },
   "outputs": [],
   "source": [
    "df_10.sub(ser_10, axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "c4Zr57f0VqK8"
   },
   "source": [
    "## 7. Function Application and Mapping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XmHli4QiVqK9"
   },
   "source": [
    "NumPy functions (element-wise array methods) operate on pandas objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "B6rIa0EDVqK-"
   },
   "outputs": [],
   "source": [
    "df_11 = np.abs(df_11)\n",
    "df_11"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "lVLmxLPEVqLF"
   },
   "source": [
    "Apply a function on 1D arrays to each column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "raphVwKhVqLH"
   },
   "outputs": [],
   "source": [
    "func_1 = lambda x: x.max() - x.min()\n",
    "df_11.apply(func_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "5dguRawpVqLK"
   },
   "source": [
    "Apply a function on 1D arrays to each row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "x9OHf4ifVqLL"
   },
   "outputs": [],
   "source": [
    "df_11.apply(func_1, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "gtGW55olVqLP"
   },
   "source": [
    "Apply a function and return a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "mvY0RDafVqLR"
   },
   "outputs": [],
   "source": [
    "func_2 = lambda x: Series([x.min(), x.max()], index=['min', 'max'])\n",
    "df_11.apply(func_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "mrHbjJZQVqLU"
   },
   "source": [
    "Apply an element-wise Python function to a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "CdGx2aFPVqLV"
   },
   "outputs": [],
   "source": [
    "func_3 = lambda x: '%.2f' %x\n",
    "df_11.applymap(func_3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6R5u0MSiVqLZ"
   },
   "source": [
    "Apply an element-wise Python function to a Series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "OmomZLbiVqLa"
   },
   "outputs": [],
   "source": [
    "df_11['a'].map(func_3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "f7fXP_CNVqLd"
   },
   "source": [
    "## 8. Sorting and Ranking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "PVamyR8eVqLk"
   },
   "outputs": [],
   "source": [
    "ser_4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "mrM0UP7qVqLo"
   },
   "source": [
    "Sort a Series by its index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "duoW8ZpqVqLp"
   },
   "outputs": [],
   "source": [
    "ser_4.sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "fgCFQmYZVqLv"
   },
   "source": [
    "Sort a Series by its values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "5EYhSIexVqLw"
   },
   "outputs": [],
   "source": [
    "ser_4.sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Ff6S5DJ3VqL1"
   },
   "outputs": [],
   "source": [
    "df_12 = DataFrame(np.arange(12).reshape((3, 4)),\n",
    "                  index=['three', 'one', 'two'],\n",
    "                  columns=['c', 'a', 'b', 'd'])\n",
    "df_12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "sGTfY4B6VqL6"
   },
   "source": [
    "Sort a DataFrame by its index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "OA-WL8jkVqL7"
   },
   "outputs": [],
   "source": [
    "df_12.sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "eHYx3zZDVqMA"
   },
   "source": [
    "Sort a DataFrame by columns in descending order:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "-ZFuH-a8VqMC"
   },
   "outputs": [],
   "source": [
    "df_12.sort_index(axis=1, ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "L8ZgqIxdVqMH"
   },
   "source": [
    "Sort a DataFrame's values by column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "VoV2bbEOVqMJ"
   },
   "outputs": [],
   "source": [
    "df_12.sort_values(by=['d', 'c'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "TtMCIRYRVqMM"
   },
   "source": [
    "Ranking is similar to numpy.argsort except that ties are broken by assigning each group the mean rank:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HOWTvKbUVqMP"
   },
   "outputs": [],
   "source": [
    "ser_11 = Series([7, -5, 7, 4, 2, 0, 4, 7])\n",
    "ser_11 = ser_11.sort_values()\n",
    "ser_11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "n8ukuqUZVqMX"
   },
   "outputs": [],
   "source": [
    "ser_11.rank()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Xkl0-p_eVqMc"
   },
   "source": [
    "Rank a Series according to when they appear in the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "i1bDu0xMVqMd"
   },
   "outputs": [],
   "source": [
    "ser_11.rank(method='first')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "rUv9ZxhOVqMh"
   },
   "source": [
    "Rank a Series in descending order, using the maximum rank for the group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "aRhQClHzVqMi"
   },
   "outputs": [],
   "source": [
    "ser_11.rank(ascending=False, method='max')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "yVlw27AHVqMo"
   },
   "source": [
    "DataFrames can rank over rows or columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "9nBIUS1zVqMp"
   },
   "outputs": [],
   "source": [
    "df_13 = DataFrame({'foo' : [7, -5, 7, 4, 2, 0, 4, 7],\n",
    "                   'bar' : [-5, 4, 2, 0, 4, 7, 7, 8],\n",
    "                   'baz' : [-1, 2, 3, 0, 5, 9, 9, 5]})\n",
    "df_13"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "xp1vTPGsVqMs"
   },
   "source": [
    "Rank a DataFrame over rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "8crpyZkvVqMu"
   },
   "outputs": [],
   "source": [
    "df_13.rank()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "f4SK2EpwVqMx"
   },
   "source": [
    "Rank a DataFrame over columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3vmlU74SVqMy"
   },
   "outputs": [],
   "source": [
    "df_13.rank(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "WjeHx6LLVqM3"
   },
   "source": [
    "## 9. Axis Indexes with Duplicate Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "z20QieDkVqM4"
   },
   "source": [
    "Labels do not have to be unique in Pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "NTxlLemxVqM5"
   },
   "outputs": [],
   "source": [
    "ser_12 = Series(range(5), index=['foo', 'foo', 'bar', 'bar', 'baz'])\n",
    "ser_12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "GiwVRejcVqM8"
   },
   "outputs": [],
   "source": [
    "ser_12.index.is_unique"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "inKAfFUoVqNA"
   },
   "source": [
    "Select Series elements:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "VIS2opqUVqNB"
   },
   "outputs": [],
   "source": [
    "ser_12['foo']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "DAjtm13VVqNL"
   },
   "source": [
    "Select DataFrame elements:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "hLAZyJ5aVqNS"
   },
   "outputs": [],
   "source": [
    "df_14 = DataFrame(np.random.randn(5, 4),\n",
    "                  index=['foo', 'foo', 'bar', 'bar', 'baz'])\n",
    "df_14"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3XIfzJIRVqNZ"
   },
   "outputs": [],
   "source": [
    "df_14.loc['bar']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "dtB3FPUEVqNh"
   },
   "source": [
    "## 10. Summarizing and Computing Descriptive Statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "AsRiusdwVqNk"
   },
   "source": [
    "Unlike NumPy arrays, Pandas descriptive statistics automatically exclude missing data.  NaN values are excluded unless the entire row or column is NA."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "KEOMbcnqVqNo"
   },
   "outputs": [],
   "source": [
    "df_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "stqI5fJ8VqN3"
   },
   "outputs": [],
   "source": [
    "df_3.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "La9O5ucUVqN6"
   },
   "source": [
    "Sum over the rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "qws2o9cAVqN7"
   },
   "outputs": [],
   "source": [
    "df_3.sum(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ZBV0i77cVqN-"
   },
   "source": [
    "Account for NaNs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "7j4aenY2VqN_"
   },
   "outputs": [],
   "source": [
    "df_3.sum(axis=1, skipna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "kqMhV3-fVqOC"
   },
   "source": [
    "## 11. Cleaning Data\n",
    "* Replace\n",
    "* Drop\n",
    "* Concatenate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "MFTTmDlcVqOD"
   },
   "outputs": [],
   "source": [
    "from pandas import Series, DataFrame\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "522vqVEkVqOG"
   },
   "source": [
    "Setup a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Q7Zhr3LVVqOH"
   },
   "outputs": [],
   "source": [
    "data_1 = {'state' : ['VA', 'VA', 'VA', 'MD', 'MD'],\n",
    "          'year' : [2012, 2013, 2014, 2014, 2015],\n",
    "          'population' : [5.0, 5.1, 5.2, 4.0, 4.1]}\n",
    "df_15 = DataFrame(data_1)\n",
    "df_15"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ShLfVMjCVqOL"
   },
   "source": [
    "### 11.1 Replace"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "v6YsVZNBVqOM"
   },
   "source": [
    "Replace all occurrences of a string with another string, in place (no copy):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "16tRnhVxVqON"
   },
   "outputs": [],
   "source": [
    "df_15.replace('VA', 'VIRGINIA', inplace=True)\n",
    "df_15"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "soCqaH-2VqOP"
   },
   "source": [
    "In a specified column, replace all occurrences of a string with another string, in place (no copy):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "NKbZOqIHVqOR"
   },
   "outputs": [],
   "source": [
    "df_15.replace({'state' : { 'MD' : 'MARYLAND' }}, inplace=True)\n",
    "df_15"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "1ii64C-_VqOV"
   },
   "source": [
    "### 11.2 Drop"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XAjnPzjpVqOW"
   },
   "source": [
    "Drop the 'population' column and return a copy of the DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "4IY7hAL4VqOX"
   },
   "outputs": [],
   "source": [
    "df_2 = df_15.drop('population', axis=1)\n",
    "df_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ySIkM1RKVqOc"
   },
   "source": [
    "### 11.3 Concatenate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "WetyYbGuVqOd"
   },
   "source": [
    "Concatenate two DataFrames:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "WutEKOrWVqOh"
   },
   "outputs": [],
   "source": [
    "data_2 = {'state' : ['NY', 'NY', 'NY', 'FL', 'FL'],\n",
    "          'year' : [2012, 2013, 2014, 2014, 2015],\n",
    "          'pop' : [6.0, 6.1, 6.2, 3.0, 3.1]}\n",
    "df_16 = DataFrame(data_2)\n",
    "df_16"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3wQP-2qEVqOk"
   },
   "outputs": [],
   "source": [
    "df_4 = pd.concat([df_1, df_16])\n",
    "df_4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aKPMt0WcVqOn"
   },
   "source": [
    "## 12. Input and Output\n",
    "* Reading\n",
    "* Writing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "tcMdYwX9VqOt"
   },
   "outputs": [],
   "source": [
    "from pandas import Series, DataFrame\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "stxLgj08VqOz"
   },
   "source": [
    "### 12.1 Reading"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "__QfdXy8VqO0"
   },
   "source": [
    "Read data from a CSV file into a DataFrame (use sep='\\t' for TSV):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "GNUUAGlXVqO1"
   },
   "outputs": [],
   "source": [
    "#install wget if you haven't \n",
    "#this is for online platform user only\n",
    "!pip install wget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "XgsTojcvVqO7"
   },
   "outputs": [],
   "source": [
    "import wget\n",
    "\n",
    "link_to_data = 'https://raw.githubusercontent.com/gaoshangdeakin/SIT384/master/ozone.csv'\n",
    "DataSet = wget.download(link_to_data)\n",
    "\n",
    "#if you load the file locally, directly use the following command\n",
    "df_1 = pd.read_csv(\"ozone.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "2u-z30SjVqO-"
   },
   "source": [
    "Get a summary of the DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3s8-j0XUVqO_"
   },
   "outputs": [],
   "source": [
    "df_1.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "xuQkJQRWVqPD"
   },
   "source": [
    "List the first five rows of the DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Ft5LyjcpVqPE"
   },
   "outputs": [],
   "source": [
    "df_1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "459fwijLVqPK"
   },
   "source": [
    "### 12.2 Writing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "QFrwvcXXVqPM"
   },
   "source": [
    "Create a copy of the CSV file, encoded in UTF-8 and hiding the index and header labels:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "YDRmmyMQVqPN"
   },
   "outputs": [],
   "source": [
    "df_1.to_csv('ozone_copy.csv', \n",
    "            encoding='utf-8', \n",
    "            index=False, \n",
    "            header=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "NN-n9CzuVqPR"
   },
   "source": [
    "View the data directory:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Bk_B6ZPJVqPS"
   },
   "outputs": [],
   "source": [
    "#for linux or mac user\n",
    "#!ls -l ./\n",
    "\n",
    "#for windows user\n",
    "!dir .\\"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "SIT742P02C-OperationsinPandas.ipynb",
   "provenance": [],
   "toc_visible": true,
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
